#coding:utf-8

import smtplib, imaplib, email, string
import re
import traceback
import xlrd
import os
import time
import pdb
from email.mime.text import MIMEText

email_dic = {}
dic_ct = {}
List_content = []
List_index = []

class SendExcel(object):
    
    def readxls(self):
        direcotry = os.getcwd() + '\\' + 'Follow Up.xlsx'
        data = xlrd.open_workbook(direcotry)
        table1 = data.sheets()[0]
        table2 = data.sheets()[1]
        table1_rs = table1.nrows
        table2_rs = table2.nrows
        for i in range(table1_rs):
            flag = str(table1.cell(i, 11).value)
            flag = flag.lower()
            if 'n' not in flag:   #有n的内容就不再保存发送
                row = table1.row_values(i)
                List_content.append(row)
        
        for i in range(table2_rs):
            cnter = str(table2.cell(i, 0).value)
            cnter = cnter.replace(' ', '').lower()
            email_dic[cnter] = table2.cell(i, 1).value        
                        
        for i in List_content:
            if len(i[1]) > 0:    #如果销售人员不为空
                sales = i[1].lower()
                List_index.append(sales)
        L_index = list(set(List_index))    #销售人员剔重

        
        for idx_sales in L_index:
            L_content = []
            for i in List_content:
                sales = i[1].lower()
                if sales == idx_sales:
                    L_content.append(i)
            dic_ct[idx_sales] = L_content   #把表格内容和销售人员对应保存到字典
            
        
    def sendHtml(self):    
        for sale, contents in dic_ct.iteritems(): 
            html_head = '''<table border="1" cellspacing="0" bordercolor="#000000" style="border-collapse: collapse">
                                <tbody>
                                    <tr>
                                        <td>Date</td>
                                        <td>Consultant</td>
                                        <td>NAME</td>
                                        <td>Application No.</td>
                                        <td>Lender</td>
                                        <td>Loan Type</td>
                                        <td>Status</td>
                                        <td>Valuation Amount</td>
                                        <td>Schedule Settlement Date</td>
                                        <td>Follow Up</td>
                                        <td>Comment</td>
                                        <td>Follow Up Date</td>
                                        <td>Settlement Day</td>
                                        <td>Settlement Loan Amount</td>
                                        <td>Pricing</td>
                                    </tr>
                                    '''
            html_tr = '''<tr>
                        <td>{0}</td>
                        <td>{1}</td>
                        <td>{2}</td>
                        <td>{3}</td>
                        <td>{4}</td>
                        <td>{5}</td>
                        <td>{6}</td>
                        <td>{7}</td>
                        <td>{8}</td>
                        <td>{9}</td>
                        <td>{10}</td>
                        <td>{11}</td>
                        <td>{12}</td>
                        <td>{13}</td>
                        <td>{14}</td>
                    </tr>
                    '''
            html_end = '''</tbody>
                            </table>'''
            html_body = ''
            
            for content in contents:
                #pdb.set_trace()
                try:
                    html_body += html_tr.format(content[0], content[1], content[3].encode('utf-8'), content[5], content[6], content[7], content[8], content[9], content[10], content[11], content[12].encode('utf-8'), content[13], content[14], content[15], content[16])
                except Exception as e:
                    print traceback.print_exc()
                
            html_content = html_head + html_body + html_end   #构造HTML表格
            
            if email_dic.has_key(sale):
                mail_addr = email_dic[sale]
                self.send_mail(mail_addr, 'Follow up', html_content)
                print "%s's mails send finished." % sale
            else:
                print '%s not found in [Follow up]-consultant table.' % sale 
            
                    
    
    def send_mail(self, mailto, sub, content):
        mail_user="xxx@tigerloans.com.au"                       #用户名
        mail_pass="xxx"                      #密码
        mail_postfix="tigerloans.com.au"                #邮箱的后缀
        mail_host="mail.tigerloans.com.au" 
        me="Case Status"+"<"+mail_user+">"
        msg = MIMEText(content, 'html', 'utf-8')  
        msg['Subject'] = sub
        msg['From'] = me
        msg['To'] = mailto                #将收件人列表以‘；’分隔
        mail_list = mailto.split(';')
        try:
            server = smtplib.SMTP()
            server.connect(mail_host)                            #连接服务器
            server.login(mail_user,mail_pass)               #登录操作
            server.sendmail(me, mail_list, msg.as_string())
            server.close()
            return True
        except Exception, e:
            print str(e)
            return False
            
def main():
    se = SendExcel()
    se.readxls()
    se.sendHtml()
    os.system('pause') 
if __name__ == '__main__':
    main()
